package com.cy.pj.dao;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class jdbcTest2 {
    @Autowired
    private DataSource dataSource;


    @Test
   void testSelectNotices01() throws SQLException {
       Connection conn = dataSource.getConnection();
        String sql="select id,title,content,status,type,createdTime "
                +" from sys_notices where id>=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,2);
        boolean flag = pstmt.execute();
        ResultSet rs = null;
        if (flag){
            rs = pstmt.getResultSet();
            List<Map<String,Object>> list = new ArrayList<>();
            while(rs.next()){
                Map<String,Object> map  = new HashMap<>();
                map.put("id",rs.getInt("id"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("status",rs.getString("status"));
                map.put("type",rs.getString("type"));
                map.put("createdTime",rs.getTimestamp("createdTime"));
                System.out.println(map);
                list.add(map);
            }
        }
        rs.close();
        pstmt.close();
        conn.close();
   }


   @Test
    void testSelectNoticces02() throws SQLException {
        Connection conn = dataSource.getConnection();
       String sql="select id,title,content,status,type,createdTime "
               +" from sys_notices where id>=?";
       PreparedStatement pstmt = conn.prepareStatement(sql);
       pstmt.setInt(1,2);
       boolean flag  = pstmt.execute();
       ResultSet rs = null;
       if (flag){
           rs=pstmt.getResultSet();
           List<Map<String,Object>> list = new ArrayList<>();
           ResultSetMetaData rsmd = rs.getMetaData();
           while(rs.next()){
               Map<String,Object> map = new HashMap<>();
               for (int i = 1; i < rsmd.getColumnCount(); i++) {
                   map.put(rsmd.getColumnLabel(i),rs.getObject(rsmd.getColumnLabel(i)));

               }
               System.out.println(map);
               list.add(map);
           }
       }
       rs.close();
       pstmt.close();
       conn.close();
   }



   @Test
    void testSelectNotices03() throws SQLException {
        Connection conn = dataSource.getConnection();
       String sql="select id,title,content,status,type,createdTime "
               +" from sys_notices where id>=?";
       PreparedStatement pstmt = conn.prepareStatement(sql);
       pstmt.setInt(1,2);
       boolean flag = pstmt.execute();
       ResultSet rs = null;
       if (flag){
           rs = pstmt.getResultSet();
           List<Map<String,Object>> list = new ArrayList<>();
           ResultSetMetaData rsmd =  rs.getMetaData();
           while (rs.next()){
               Map<String,Object> map = new HashMap<>();
               for (int i = 1; i < rsmd.getColumnCount(); i++) {
                   map.put(rsmd.getColumnLabel(i),rs.getObject(rsmd.getColumnLabel(i)));
               }

               System.out.println(map);
               list.add(map);
           }
       }
       rs.close();
       pstmt.close();
       conn.close();
   }


   @Test
    void testSelectNotices04() throws SQLException {
        Connection conn = dataSource.getConnection();
       String sql="select id,title,content,status,type,createdTime "
               +" from sys_notices where id>=?";
       PreparedStatement statement = conn.prepareStatement(sql);
       statement.setInt(1,2);
       boolean flag = statement.execute();
       ResultSet rs  = null;
       if (flag){
           rs = statement.getResultSet();
           List<Map<String,Object>> list = new ArrayList<>();
           ResultSetMetaData rsmd = rs.getMetaData();
           while(rs.next()){
               Map<String,Object> map = new HashMap<>();
               for (int i = 1; i < rsmd.getColumnCount(); i++) {
                   map.put(rsmd.getColumnLabel(i),rs.getObject(rsmd.getColumnLabel(i)));
               }
               System.out.println(map);
               list.add(map);
           }
       }
       rs.close();
       statement.close();
       conn.close();
   }
}
